# -*- coding: utf-8 -*-
import os
import re
import pandas as pd
from flask import Flask, jsonify, request
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['ALLOWED_EXTENSTIONS'] = {'xlsx', 'xls'}
DB_CONFIG = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "root",
    "port": "3305",
    "database": "school_paper"
}

def get_connection():
    return mysql.connector.connect(**DB_CONFIG)
def is_valid_email(email):
    return re.match(r"^[\w\.-]+@[\w\.-]+\.\w+$", str(email)) is not None
def is_valid_phone(phone):
    return re.match(r"^1[3-9]\d{9}$", str(phone)) is not None
def get_facultyId(faculty_name:str) -> int:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_facultyId", (f"%{faculty_name}%",))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["faculty_id"]
    except Error as e:
        print("匹配学院失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_majorId(major_name:str) -> int:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_majorId", (f"%{major_name}%",))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["major_id"]
        return False
    except Error as e:
        print("匹配专业失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_userIdByAccount(account:str) -> int:
    conn, cursor = None, None
    try:
        if account:
            conn = get_connection()
            cursor = conn.cursor(dictionary=True)
            result = cursor.callproc("select_userIdByAccount",(account, 0))
            return result["select_userIdByAccount_arg2"]
        else:
            return None
    except Error as e:
        print("匹配用户失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
@app.route('/uploadTeachers', methods=['POST'])
def upload_teachers():
    if 'file' not in request.files:
        return jsonify({"error": "没有上传文件"}), 400

    file = request.files['file']
    if not file.filename.endswith(('.xls', '.xlsx')):
        return jsonify({"error": "无法识别文件格式"}), 400

    # 保存临时文件
    filepath = os.path.join("uploads", file.filename)
    os.makedirs("uploads", exist_ok=True)
    file.save(filepath)

    try:
        # 读取 Excel
        df = pd.read_excel(filepath)

        # 必要字段检测
        required_columns = ["工号", "姓名", "学院", "邮箱", "手机号", "类型"]
        for col in required_columns:
            if col not in df.columns:
                return jsonify({"error": f"缺少列: {col}"}), 400

        # 数据验证
        errors = []
        valid_users = []

        conn = get_connection()
        cursor = conn.cursor(dictionary=True)

        for idx, row in df.iterrows():
            account = str(row["工号"]).strip()
            name = str(row["姓名"]).strip()
            type = str(row["类型"]).strip()
            email = str(row["邮箱"]).strip() if not pd.isna(row["邮箱"]) else None
            phone = str(row["手机号"]).strip() if not pd.isna(row["手机号"]) else None
            faculty_name = str(row["学院"]).strip()
            if not account or not name or not faculty_name:
                errors.append(f"第 {idx + 2} 行: 工号/姓名/学院不能为空")
                continue
            if email and not is_valid_email(email):
                errors.append(f"第 {idx + 2} 行: 邮箱格式错误 {email}")
                continue
            if phone and not is_valid_phone(phone):
                errors.append(f"第 {idx + 2} 行: 手机号格式错误 {phone}")
                continue

            faculty_id = get_facultyId(faculty_name)
            if not faculty_id:
                errors.append(f"第 {idx + 2} 行: 学院 '{faculty_name}' 不存在")
                continue
            teacher_type = 1 if type == "校内" else 0
            role_id = 2
            # 唯一性检测
            cursor.callproc("select_userCount", (account,))
            for result in cursor.stored_results():
                row = result.fetchone()
                if row and row["cnt"] > 0:
                    errors.append(f"第 {idx + 2} 行: 工号 {account} 已存在")
                    continue

            valid_users.append((account, name, email, phone, faculty_id, teacher_type, role_id))

        # 批量写入数据库
        if valid_users:
            for user in valid_users:
                cursor.callproc("add_teachers", user)
            conn.commit()

        cursor.close()
        conn.close()

        return jsonify({
            "inserted": len(valid_users),
            "errors": errors
        })
    except Exception as e:
        import traceback
        print("Flask 发生异常：", e)
        traceback.print_exc()  # 打印完整错误堆栈
        return jsonify({"error": str(e)}), 500
    finally:
        if os.path.exists(filepath):
            os.remove(filepath)

@app.route('/uploadStudents', methods=['POST'])
def upload_students():
    if 'file' not in request.files:
        return jsonify({"error": "没有上传文件"}), 400

    file = request.files['file']
    if not file.filename.endswith(('.xls', '.xlsx')):
        return jsonify({"error": "无法识别文件格式"}), 400

    # 保存临时文件
    filepath = os.path.join("uploads", file.filename)
    os.makedirs("uploads", exist_ok=True)
    file.save(filepath)

    try:
        # 读取 Excel
        df = pd.read_excel(filepath)

        # 必要字段检测
        required_columns = ["学号", "姓名", "专业", "学院", "入学年份", "指导老师1工号", "指导老师1姓名", "指导老师2工号", "指导老师2姓名", "邮箱", "手机号"]
        for col in required_columns:
            if col not in df.columns:
                return jsonify({"error": f"缺少列: {col}"}), 400

        # 数据验证
        errors = []
        valid_users = []

        conn = get_connection()
        cursor = conn.cursor(dictionary=True)

        for idx, row in df.iterrows():
            account = str(row["学号"]).strip()
            name = str(row["姓名"]).strip()
            email = str(row["邮箱"]).strip() if not pd.isna(row["邮箱"]) else None
            phone = str(row["手机号"]).strip() if not pd.isna(row["手机号"]) else None
            major_name = str(row["专业"]).strip()
            faculty_name = str(row["学院"]).strip()
            grade = int(row["入学年份"])
            teacher_account = str(row["指导老师1工号"]).strip()
            teacher_name = str(row["指导老师1姓名"]).strip()
            teacher2_account = str(row["指导老师2工号"]).strip()
            teacher2_name = str(row["指导老师2姓名"]).strip()

            if not account or not name or not major_name or not faculty_name or not grade or not teacher_account or not teacher_name:
                errors.append(f"第 {idx + 2} 行: 学号/姓名/专业/学院/入学年份/指导老师1工号/指导老师1姓名不能为空")
                continue
            if email and not is_valid_email(email):
                errors.append(f"第 {idx + 2} 行: 邮箱格式错误 {email}")
                continue
            if phone and not is_valid_phone(phone):
                errors.append(f"第 {idx + 2} 行: 手机号格式错误 {phone}")
                continue

            faculty_id = get_facultyId(faculty_name)
            if not faculty_id:
                errors.append(f"第 {idx + 2} 行: 学院 '{faculty_name}' 不存在")
                continue
            major_id = get_majorId(major_name)
            if not major_id:
                errors.append(f"第 {idx + 2} 行: 专业 '{major_name}' 不存在")
                continue
            teacher = get_userIdByAccount(teacher_account)
            teacher2 = None
            if teacher2_name and str(teacher2_name).strip():
                teacher2 = get_userIdByAccount(teacher2_account)
            role_id = 3
            # 唯一性检测
            cursor.callproc("select_userCount", (account,))
            for result in cursor.stored_results():
                row = result.fetchone()
                if row and row["cnt"] > 0:
                    errors.append(f"第 {idx + 2} 行: 学号 {account} 已存在")
                    continue

            valid_users.append((account, name, email, phone, major_id, faculty_id, grade, teacher, teacher2, role_id))

        # 批量写入数据库
        if valid_users:
            for user in valid_users:
                cursor.callproc("add_students", user)
            conn.commit()

        cursor.close()
        conn.close()

        return jsonify({
            "inserted": len(valid_users),
            "errors": errors
        })
    except Exception as e:
        import traceback
        print("Flask 发生异常：", e)
        traceback.print_exc()  # 打印完整错误堆栈
        return jsonify({"error": str(e)}), 500
    finally:
        if os.path.exists(filepath):
            os.remove(filepath)

if __name__ == '__main__':
    app.run()
